package com.wondersgroup.fjzlpt.console.dao.impl.yljgjfsb;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.console.dao.inter.yljgjfsb.JfsbDAO;
import com.wondersgroup.fjzlpt.console.exp.ExecuteSqlException;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * 机房设备数据访问层
 * <p>
 * <p>
 * Created by sanma on 2017/8/14.
 */
@Repository
public class JfsbDAOImpl  extends BaseJTDAOImpl implements JfsbDAO{

    @Autowired
    private DialectProxy dialectProxy;

    @Override
    @Autowired
    @Qualifier("TB_ZD_YLJGJFXX")
    public void setTb(TableBean tb) {
        super.setTb(tb);
    }

    @Autowired
    @Qualifier("TB_ZD_YLJGSBXX")
    private TableBean yljgsbxxTable;

    @Autowired
    @Qualifier("TB_ZD_YLJGJFLX")
    private TableBean jfflTable;

    @Override
    public JSONObject queryPageJfxx(Map<String,String> map) {
        String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());

        StringBuilder sbud = new StringBuilder("SELECT A.*,D.LX ,D.LXMC , F.SBSL FROM ").append(this.tb.getTableName()).append(" A LEFT JOIN (SELECT B.JFID , \n");

        if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
            sbud.append("GROUP_CONCAT(B.LX) LX , GROUP_CONCAT(C.XMMC) LXMC FROM TB_ZD_YLJGJFLX B LEFT JOIN TB_ZY_JFLX C  \n");
        }else if("LISTAGG".equals(groupFunName.toUpperCase())){
            sbud.append("listagg(B.LX,',') within group(order by null) LX , listagg(C.XMMC,',') within group(order by null) LXMC FROM TB_ZD_YLJGJFLX B LEFT JOIN TB_ZY_JFLX C  \n");
        }else{
            throw new RuntimeException("无法确定分组函数");
        }
        sbud.append("ON B.LX = C.XMDM GROUP BY B.JFID) D ON A.JFID = D.JFID LEFT JOIN TB_ZD_YLJGXX E ON A.PTJGID = E.PTJGID \n");
        sbud.append(" LEFT JOIN ( SELECT JFID , COUNT(1) SBSL FROM TB_ZD_YLJGSBXX GROUP BY JFID ) F ON A.JFID = F.JFID \n");
        sbud.append("  WHERE A.ZT = '1' ");

        List list = new ArrayList();
        String value = map.get("PTJGID");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and A.PTJGID = ? ");
            list.add(value);
        }
        value = map.get("YLJGMC");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and E.YLJGMC like ? ");
            list.add("%" + value+"%");
        }
        value = map.get("KSDM");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and A.KSDM = ? ");
            list.add(value);
        }
        value = (String)map.get("XZQHDM");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and E.XZQHDM like ? \n");
            list.add(value+"%");
        }

        PageQuerySQLBean sb = checkAndCreatePageQuerySQLBean(map, sbud.toString(), null, list.toArray());
        return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
    }

    @Override
    public JSONArray querySbxxById(Map map) {

        StringBuilder sbud = new StringBuilder("select * from ").append(yljgsbxxTable.getTableName())
                .append(" where JFID = ? ").append(" order by GMRQ ");
        SQLBean sql = new SQLBean(sbud.toString(), new Object[]{map.get("JFID")});
        return super.queryList(sql);

    }

    @Override
    public void addJfxx(Map map){
        try {
            String jffl = map.remove("LX").toString();
            String jfid = UUID.randomUUID().toString();
            insertJfflxx(jffl,jfid);
            map.put("JFID" , jfid);
            map.put("ZT" , "1");
            this.insert(map);
        }catch (Exception e ){
            throw new ExecuteSqlException("新增机房信息出错了：" + e.getMessage() , e);
        }
    }

    @Override
    public void updateJfxx(Map map) {
        try {
            String jffl = map.remove("LX").toString();
            String jfid = map.get("JFID").toString();
            SQLBean deleteJffl = new SQLBean("delete from " + jfflTable.getTableName() + " where JFID = ? " , jfid);
            this.update(deleteJffl);
            insertJfflxx(jffl,jfid);
            this.updateForPrimaryKey(map);
        }catch (Exception e ) {
            throw new ExecuteSqlException("修改机房信息出错了：" + e.getMessage() , e);
        }
    }

    @Override
    public void deleteJfxx(Map map) {
        try {
            this.updateForPrimaryKey(map);
        }catch (Exception e ) {
            throw new ExecuteSqlException("删除机房信息出错了：" + e.getMessage() , e);
        }
    }

    @Override
    public void addSbxx(Map map) {
        try {
            map.put("SBID",UUID.randomUUID().toString());
            SQLBean insert = yljgsbxxTable.getInsertSQLBean(map);
            this.update(insert);
        }catch (Exception e ) {
            throw new ExecuteSqlException("新增设备信息出错了：" + e.getMessage() , e);
        }
    }

    @Override
    public void updateSbxx(Map map) {
        try {
            SQLBean update = yljgsbxxTable.getUpdateSQLBean(map);
            this.update(update);
        }catch (Exception e ) {
            throw new ExecuteSqlException("修改设备信息出错了：" + e.getMessage() , e);
        }
    }

    @Override
    public void deleteSbxx(Map map) {
        try {
            SQLBean update = yljgsbxxTable.getDeleteSQLBean(map);
            this.update(update);
        }catch (Exception e ) {
            throw new ExecuteSqlException("删除设备信息出错了：" + e.getMessage() , e);
        }
    }


    private void insertJfflxx(String jffl , String jfid){
        if(!StringUtils.isEmpty(jffl)){
            String[] jfflAry = jffl.split(",");
            List<Map> list = new ArrayList<>();
            for(String fl : jfflAry){
                Map params = new HashMap();
                params.put("JFID" , jfid);
                params.put("LX" , fl);
                list.add(params);
            }
            SQLBean jfflSqlBean = jfflTable.getInsertBatchSQLBean(list);
            this.update(jfflSqlBean);
        }
    }

    /**
     * 查询机房及其对应设备数据的用于导出
     * @param map
     */
    public JSONArray queryAllForExcel(Map<String,String> map){
        String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());

        StringBuilder sbud = new StringBuilder("SELECT A.*,D.LX ,D.LXMC,e.SBBH,e.SBMC,e.CD,e.SCCJ,e.SBXH,e.GMRQ,e.XJQK,e.GMDJ,e.SYQK FROM ");
        sbud.append(this.tb.getTableName()).append(" A LEFT JOIN (SELECT B.JFID , \n");
        if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
            sbud.append("GROUP_CONCAT(B.LX) LX ,GROUP_CONCAT(C.XMMC) LXMC FROM TB_ZD_YLJGJFLX B LEFT JOIN TB_ZY_JFLX C  \n");
        }else if("LISTAGG".equals(groupFunName.toUpperCase())){
            sbud.append("listagg(B.LX,',') within group(order by null) LX ,listagg(C.XMMC,',') within group(order by null) LXMC FROM TB_ZD_YLJGJFLX B LEFT JOIN TB_ZY_JFLX C  \n");
        }else{
            throw new RuntimeException("无法确定分组函数");
        }

        sbud.append("ON B.LX = C.XMDM GROUP BY B.JFID) D ON A.JFID = D.JFID \n");//append(yljgsbxxTable.getTableName())
        sbud.append("left join ").append(yljgsbxxTable.getTableName()).append(" e on a.JFID=e.JFID ");
        sbud.append(" LEFT JOIN TB_ZD_YLJGXX F ON a.PTJGID = F.PTJGID \n");
        sbud.append("WHERE A.ZT = '1' ");
        List list = new ArrayList();
        String value = map.get("PTJGID");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and A.PTJGID = ? ");
            list.add(value);
        }
        value = map.get("KSDM");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and A.KSDM = ? ");
            list.add(value);
        }
        value = map.get("XZQHDM");
        if(!StringUtils.isEmpty(value))
        {
            sbud.append("and F.XZQHDM LIKE ? ");
            list.add(value + "%");
        }
        SQLBean bean = new SQLBean(sbud.toString(),list.toArray());

        return super.queryList(bean);
    }

}
